<?php
namespace app\admin\service;

use app\BaseService;
use think\exception\ValidateException;

class ExcelService extends BaseService
{

    public function saveMergeDataToFile($data, $title)
    {
        $objPHPExcel = new \PHPExcel();
        try {
            $lettersUpper = range('A', 'Z');
            $objSheet = $objPHPExcel->getActiveSheet();
            $objSheet->setTitle($title);
            $styleArray = array(
                'font' => array(
                    'color' => array('rgb' => 'FF0000'), // 红色
                    'bold' => true
                ),
            );
            $columnsKey = array_keys($data['columns']);
            $columns = array_values($data['columns']);
            $i = 1;
            $j = 2;
            $x = 3;
            foreach ($data['list'] as $k => $rs) {
                $mergeCells = 'A'.($i).':E'.($i);
                //设置合并单元格
                $objSheet->mergeCells($mergeCells);
                $lineCellKey = 'A'.$i;
                //设置线路
                $objSheet->setCellValue($lineCellKey,  $rs['line_name']);
                $objSheet->getStyle($lineCellKey)->applyFromArray($styleArray);
                //设置表格头
                foreach ($columns as $k1 => $rs1) {
                    $cellKey = $lettersUpper[$k1].($j);
                    $objSheet->setCellValue($cellKey,  $rs1);
                }
                //设置表格数据
                foreach ($rs['shift'] as $k2 => $val) {
                    foreach ($columnsKey as $k3 => $val3) {
                        $cellKey2 = $lettersUpper[$k3].($x+$k2);
                        $objSheet->setCellValue($cellKey2,  $val[$val3]);
                    }
                }
                //计算每行的位置
                $j = $j + count($rs['shift']) + 2;
                $i = $i + count($rs['shift']) + 2;
                $x = $x + count($rs['shift']) + 2;
            }

            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $fileName= storage_path('excel',$title.".xlsx");
            if (!file_exists($fileName)) {
                $objWriter->save($fileName);
            }
            return $fileName;

        } catch (\PHPExcel_Exception $e) {
            throw new ValidateException($e->getMessage());
        }
    }


    public function saveDataToFile($data, $title)
    {
        $objPHPExcel = new \PHPExcel();
        try {
            $lettersUpper = range('A', 'Z');
            $objSheet = $objPHPExcel->getActiveSheet();
            //工作表标题
            $objSheet->setTitle($title);
            $columnsKey = array_keys($data['columns']);
            $columns = array_values($data['columns']);
            if (!empty($data['line'])) {
                $mergeCells = 'A1:'.$lettersUpper[count($columnsKey)-1].'1';
                //设置合并单元格
                $objSheet->mergeCells($mergeCells);
                $objSheet->setCellValue("A1",  $data['line']['name']);
                foreach ($columns as $k => $rs) {
                    $objSheet->setCellValue($lettersUpper[$k]."2",  $rs);
                }
                foreach ($data['list'] as $k => $rs) {
                    $k += 3;
                    $i = 0;
                    foreach ($rs as $key => $val) {
                        if (in_array($key,$columnsKey)) {
                            $objSheet->setCellValue($lettersUpper[$i].$k, "$val");
                            $i++;
                        }
                    }
                }
            } else {
                foreach ($columns as $k => $rs) {
                    $objSheet->setCellValue($lettersUpper[$k]."1",  $rs);
                }
                foreach ($data['list'] as $k => $rs) {
                    $k += 2;
                    $i = 0;
                    foreach ($rs as $key => $val) {
                        if (in_array($key,$columnsKey)) {
                            $objSheet->setCellValue($lettersUpper[$i].$k, "$val");
                            $i++;
                        }
                    }
                }
            }

            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $fileName= storage_path('excel',$title.".xlsx");
            if (file_exists($fileName)) {
                unlink($fileName);
            }
            $objWriter->save($fileName);
            return $fileName;
            /*//第一行内容，放列标题
            $objSheet->setCellValue("A1", "城市");
            $objSheet->setCellValue("B1", "阿姨姓名");
            $objSheet->setCellValue("C1", "阿姨手机号");
            $objSheet->setCellValue("D1", "渠道");
            $objSheet->setCellValue("E1", "创建时间");
            //设置第一行的背景颜色
            $objPHPExcel->getActiveSheet()
                ->getStyle('A1:E1')
                ->getFill()
                ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB('FFFF00');
            //后面行循环放内容
            for($i=2;$i<10;$i++){
                $objSheet->setCellValue("A".$i, "北京");
                $objSheet->setCellValue("B".$i, "陶士涵");
                $objSheet->setCellValue("C".$i, "18805419888\t");
                $objSheet->setCellValue("D".$i, "外呼");
                $objSheet->setCellValue("E".$i, "2021-08-1 19:17:36\t");
            }
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $fileName= storage_path('excel',"outcall_result.xlsx");
            $objWriter->save($fileName);*/
        } catch (\PHPExcel_Exception $e) {
            throw new ValidateException($e->getMessage());
        }
    }
}